package com.szholly.pro.watergas_server.ps.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.szholly.pro.watergas_server.ps.monitor.MonitorDataType;
import com.szholly.pro.watergas_server.ps.monitor.MonitorWaterType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.web.RequestUnit;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

import cn.jiguang.common.utils.StringUtils;
import net.sf.ehcache.search.expression.And;

@Service
public class DataModificationService {

	private static final Logger logger = LoggerFactory.getLogger(DataModificationService.class);
	
	private final String _hourData = "3";
	private final String _dayData = "4";
	private final String _cycleData = "6";
	
	MonitorWaterType monitorWaterType=new MonitorWaterType();
	/**
	 * 修约数据
	 * gislin
	 * 2016年6月27日
	 * @param mn 设备编号
	 * @param qn 数据编号
	 * @param datatime 数据时间
	 * @param dataSource 数据类型
	 * @param newData 新数据
	 * @param certificateId 修约凭证
	 */
	public void modifyData(String mn,String qn,String  datatime,String dataSource,String newData,String dataFlag,String userId,String certificateId,String waterType){
		

		String dataTableName = "";
		if(StringUtil.IsNotNullOrEmpty(waterType)&&!waterType.equals("3")){
			String tableType="hour";
			if(dataSource.equals("3")){
				tableType="hour";
			}
			else if(dataSource.equals("4")){
				tableType="day";
			}
			else if(dataSource.equals("6")){
				tableType="cycle";
			}
			else{
				return;
			}
			MonitorDataType md=monitorWaterType.getWaterType(waterType, tableType);
			dataTableName=md.getTableName();
		}
		else{
			if(_hourData.equals(dataSource)){
				dataTableName = "BD_MO_HOUR";
			}
			else if(_dayData.equals(dataSource)){
				dataTableName = "BD_MO_DAY";
			}
			else if(_cycleData.equals(dataSource)){
				dataTableName = "BD_MO_Cycle";
			}
			else{
				return;
			}
			
			if(StringUtil.IsNullOrEmpty(newData)){
				return;
			}
		}
		
		
		List<GlobalParameter> pc = new ArrayList<>();
		pc.add(new GlobalParameter("mn", mn));
		
		if(certificateId == null || certificateId.toLowerCase().equals("null")){
			certificateId = "";
		}
		pc.add(new GlobalParameter("certificateId",certificateId));
		
		try {
			DataUtil.beginTransaction();
		
			String insertNewSql = " INSERT INTO "+dataTableName+" (GUID,QN,DATATIME,MN,COU,AVG,PARMNAME,CREATETIME,MODIFY_USER_ID,MODIFY_DATE,MODIFY_CERTIFICATE_ID,DATA_FROM,FLAG)";
			
			String[] dataItem = newData.split(" ");
			Pattern p = Pattern.compile("poll:(.*),cou:(.*),avg:(.*),flag:(.*)");
			for(int i=0;i<dataItem.length;i++){
				Matcher m = p.matcher(dataItem[i]);
				
				if(!m.find()){
					continue;
				}
				
				String pollCode = m.group(1);
				String couStr = m.group(2);
				String avgStr = m.group(3);
				String flagStr = m.group(4);
				
				//先把历史数据存档
				if(StringUtil.IsNotNullOrEmpty(datatime) && !"null".equals(datatime)){
					String insertHistorySql = " INSERT INTO MONITOR_DATA_MODIFY_HISTORY(GUID,QN,ST,CN,DATATIME,MN,COU,MAX,MIN,AVG,PARMNAME,CREATETIME,FLAG,DATA_TYPE,MODIFY_USER_ID,MODIFY_DATE,MODIFY_CERTIFICATE_ID,DATA_FROM) \n"
							+ " SELECT GUID,QN,ST,CN,DATATIME,MN,COU,MAX,MIN,AVG,PARMNAME,CREATETIME,FLAG,'"+dataSource+"',MODIFY_USER_ID,MODIFY_DATE,MODIFY_CERTIFICATE_ID,DATA_FROM"
							+ " FROM "+dataTableName+" WHERE MN=@mn "
							+ " and datatime =to_date('"+datatime+"','yyyy-mm-dd hh24:mi:ss')"
							+ "  and PARMNAME='"+pollCode+"'";
					String deleteDataSql = " DELETE FROM "+dataTableName + " WHERE mn =@mn and  PARMNAME='"+pollCode+"'"
							+ " and datatime =to_date('"+datatime+"','yyyy-mm-dd hh24:mi:ss')";
					String deleteHistorySql = " DELETE FROM MONITOR_DATA_MODIFY_HISTORY WHERE mn =@mn  and datatime =to_date('"+datatime+"','yyyy-mm-dd hh24:mi:ss') and PARMNAME='"+pollCode+"'";
					
					pc.add(new GlobalParameter("qn", qn));
					pc.add(new GlobalParameter("datatime", datatime));
					DataUtil.ExecuteUpdateSql(deleteHistorySql, pc);
					DataUtil.ExecuteUpdateSql(insertHistorySql, pc);
					DataUtil.ExecuteUpdateSql(deleteDataSql, pc);
				}
				else{
					SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
					qn = sf.format(datatime);
					pc.add(new GlobalParameter("qn", qn));
				}
				
				
				Double avg = null;
				try {
					avg = Double.parseDouble(avgStr);
				} catch (Exception e) {
					continue;
				}
				
				Double cou = null;
				try {
					cou = Double.parseDouble(couStr);
				} catch (Exception e) {
					cou = 0d;
				} 
				
				pc.add(new GlobalParameter("datatime",datatime));
				pc.add(new GlobalParameter(i+"cou",cou));
				pc.add(new GlobalParameter(i+"avg",avg));
				pc.add(new GlobalParameter(i+"flag",flagStr));
				pc.add(new GlobalParameter(i+"parmname",pollCode));
				pc.add(new GlobalParameter("userId",userId));
				
				insertNewSql += "\n SELECT SYS_GUID(),@qn,to_date('"+datatime+"','yyyy-mm-dd hh24:mi:ss'),@mn,@"+i+"cou,@"+i+"avg,@"+i+"parmname,sysdate,@userId,sysdate,@certificateId,'2',@"+i+"flag from dual "
						+ "\n union all";
			}
			
			insertNewSql = lrq.common.StringUtil.replaceLast(insertNewSql, "union all", "");
			String upSql="   update "+dataTableName+" set DATA_FROM='2',qn='"+qn+"',DATATIME=to_date('"+datatime+"','yyyy-mm-dd hh24:mi:ss') where  MN ='"+mn+"' and   datatime =to_date('"+datatime+"','yyyy-mm-dd hh24:mi:ss')";
			DataUtil.ExecuteUpdateSql(insertNewSql, pc);			
			DataUtil.ExecuteUpdateSql(upSql, pc);
			DataUtil.commitTransaction();
		} catch (Exception e) {
			DataUtil.rollbackTransaction();
			e.printStackTrace();
		}
		
		
	
	}

	/**
	 * 获取未修约的数据
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getNotModifyDataList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();

		String mn = params.get("mn");// 设备唯一编号
		String start = params.get("start");
		String end = params.get("end");

		String sql = "select *  from (select t.datatime,t.parmname,t.avg,t.avg_src,t.cou,t.cou_src,t.data_flag,t.data_from from BD_MO_MODIFY_HOUR t ";
		sql += " WHERE t.MN = '" + mn + "'";
		sql += " and t.DATATIME >= to_date('" + start + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		sql += " and t.DATATIME <= to_date('" + end + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))";
		
		sql += " pivot(sum(avg_src) as a, sum(avg) as b, sum(cou_src) as c, sum(cou) as d, max(data_flag) as e, max(data_from) as f for parmname  in ('B01' as PB01, '011' as P011, '060' as P060))";

		sql += " order by DATATIME desc";

		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 获取已经修约的数据
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getModifiedDataList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();

		String mn = params.get("mn");// 设备唯一编号

		String sql = "SELECT t.DATATIME,t.parmname,d.s_domaincaption as paramname,mdc.*,d1.s_domaincaption as CERTIFICATE_TYPENAME,u.C_USEREALNAME"
				+ " from BD_MO_MODIFY_HOUR t "
				+ " left join monitor_data_certificate mdc on t.certificate_id=mdc.guid "
				+ " left join (select s_domaincode,s_domaincaption from data_defulatdomain where s_domainname='凭证类型') d1 on mdc.certificate_type=d1.s_domaincode"
				+ " left join (select s_domaincode,s_domaincaption from data_defulatdomain where s_domainname='MONITOR_PARAM') d on t.parmname=d.s_domaincode"
				+ " left join safe_user u on mdc.user_id=u.S_USERID WHERE t.MN = '" + mn + "'";

		sql += " and t.DATATIME >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		sql += " and t.DATATIME <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";

		sql += " and t.data_from > 1";// 修约数据

		// 修约方法
		if (params.containsKey("modify_method")) {
			sql += " and t.modify_method=@modify_method";
			listParam.add(new GlobalParameter("modify_method", params.get("modify_method")));
		}

		// 修约凭证
		if (params.containsKey("modify_certificate")) {
			sql += " and t.certificate_id is not null";
		} else {
			sql += " and t.certificate_id is null";
		}

		sql += " order by DATATIME desc";

		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}

	/**
	 * 人工修约: 先把需要修约的参数值修约，再把数据来源修改为人工修约，状态为2（直出数据人工修约) 3(人工监测数据人工修约)
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public int updateModifiedData(Map<String, String> params) throws Exception {

		String mn = params.get("mn");// 设备唯一编号
		String data_times = params.get("data_times");
		String P011 = params.get("P011AVG");
		String PB01 = params.get("PB01AVG");
		String P060 = params.get("P060AVG");

		String updateSql = "";

		String[] data_time_arr = data_times.split(",");

		String commonUpdate = "update BD_MO_MODIFY_HOUR set modify_method = '0',modify_time=sysdate";
		String commonWhere = " where mn = '" + mn + "'";
		String timeWhere = "";

		int result = 0;
		try {
			DataUtil.beginTransaction();
			for (String time : data_time_arr) {
				timeWhere = " and trunc(datatime,'hh24')=trunc(to_date('" + time + "','yyyy-MM-dd HH24:MI:SS'),'hh24')";

				if (StringUtil.IsNotNullOrEmpty(P011)) {
					updateSql = commonUpdate + ", data_from=bitand(nvl(data_from,0),1)+2, avg = " + P011 + commonWhere + " and parmname='011'" + timeWhere;
					result += DataUtil.ExecuteUpdateSql(updateSql, null);
					
					updateSql = "update BD_MO_MODIFY_HOUR set cou = " + P011  + "*(select avg from BD_MO_MODIFY_HOUR t "+commonWhere + " and parmname='B01'" + timeWhere+")"
							+ commonWhere + " and parmname='011'" + timeWhere;
					result += DataUtil.ExecuteUpdateSql(updateSql, null);
				}
				
				if (StringUtil.IsNotNullOrEmpty(P060)) {
					updateSql = commonUpdate + ", data_from=bitand(nvl(data_from,0),1)+2, avg = " + P060 + commonWhere + " and parmname='060'" + timeWhere;
					result += DataUtil.ExecuteUpdateSql(updateSql, null);
					
					updateSql = "update BD_MO_MODIFY_HOUR set cou = " + P060  + "*(select avg from BD_MO_MODIFY_HOUR t "+commonWhere + " and parmname='B01'" + timeWhere+")"
							+ commonWhere + " and parmname='011'" + timeWhere;
					result += DataUtil.ExecuteUpdateSql(updateSql, null);
				}
				
				//流量最后更新，如更新，则其他参数的排放量需要修改
				if (StringUtil.IsNotNullOrEmpty(PB01)) {
					updateSql = commonUpdate + ", data_from=bitand(nvl(data_from,0),1)+2, avg = " + PB01 + commonWhere + " and parmname='B01'" + timeWhere;
					result += DataUtil.ExecuteUpdateSql(updateSql, null);
					
					updateSql = "update BD_MO_MODIFY_HOUR  set modify_method = '0',modify_time=sysdate, data_from=bitand(nvl(data_from,0),1)+2,cou=avg*0.001*"+PB01
							+ commonWhere + " and parmname in ('P011','P060') " + timeWhere;
					result += DataUtil.ExecuteUpdateSql(updateSql, null);
				}
			}
			
			DataUtil.commitTransaction();

		} catch (Exception e) {
			DataUtil.rollbackTransaction();
		}

		return result;
	}

	public List<RowBase> getCertificateList(Map<String, String> params, PageInfo pageInfo) throws Exception {
		String sql = "select t.*,d.s_domaincaption as certificate_typename,u.c_userealname from monitor_data_certificate t"
				+ " left join safe_user u on t.user_id=u.s_userid"
				+ " left join (select s_domaincode,s_domaincaption from data_defulatdomain where s_domainname='凭证类型') d on t.certificate_type=d.s_domaincode"
				+ " WHERE 1=1 ";

		if(params.containsKey("WATER_TYPE")){
			sql += " and t.WATER_TYPE='"+params.get("WATER_TYPE")+"'";
		}
		if(params.containsKey("start")){
			sql += " and t.CREATE_TIME >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		
		if(params.containsKey("end")){
			sql += " and t.CREATE_TIME <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql += " order by CREATE_TIME desc";

		return DataUtil.getPageRow(sql, null, null, pageInfo);
	}

	/**
	 * 获取凭证关联的数据数量
	 * 
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public int getModifyDataCountByCertificate(Map<String, String> params) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("certificate_id", params.get("certificate_id")));
		String sql = "select count(*) from BD_MO_MODIFY_HOUR t where t.certificate_id =@certificate_id";
		return DataUtil.getScaleInt(sql, listParam);
	}

	/**
	 * 删除凭证
	 * 
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public boolean deleteCertificate(Map<String, String> params) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("certificate_id", params.get("certificate_id")));
		try {
			DataUtil.beginTransaction();
			// 1.删除关联的凭证文件
			String sql = "delete from monitor_data_certificate_file t where t.certificate_id =@certificate_id";
			DataUtil.ExecuteUpdateSql(sql, listParam);

			// 2.删除修约数据上的凭证
			sql = "update BD_MO_MODIFY_HOUR t set t.certificate_id = null where t.certificate_id =@certificate_id";
			DataUtil.ExecuteUpdateSql(sql, listParam);

			// 3.删除凭证
			sql = "delete from monitor_data_certificate where guid=@certificate_id";
			DataUtil.ExecuteUpdateSql(sql, listParam);

			DataUtil.commitTransaction();
			return true;
		} catch (Exception ex) {
			DataUtil.rollbackTransaction();
			logger.error(ex.toString());
			return false;
		}
	}

	/**
	 * 删除单个凭证文件
	 * 
	 * @param guid
	 * @return
	 * @throws Exception
	 */
	public boolean deleteCertificateFile(String guid) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("guid", guid));
		if (StringUtil.IsNullOrEmpty(guid))
			return false;
		try {
			DataUtil.beginTransaction();
			String sql = "delete from monitor_data_certificate_file t where t.guid =@guid";
			DataUtil.ExecuteUpdateSql(sql, listParam);
			DataUtil.commitTransaction();
			return true;
		} catch (Exception ex) {
			DataUtil.rollbackTransaction();
			logger.error(ex.toString());
			return false;
		}
	}
	
	/**
	 * 获取凭证信息
	 * gislin
	 * 2016年7月1日
	 * @return
	 * @throws Exception 
	 */
	public RowBase getCertificate(String certificateId) throws Exception{
		String sql = " SELECT * FROM monitor_data_certificate WHERE GUID = @GUID ";
		List<GlobalParameter> pc = new ArrayList<>();
		pc.add(new GlobalParameter("GUID", certificateId));
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("MONITOR_DATA_CERTIFICATE");
		
		List<RowBase> list = DataUtil.getPageList(sql, pc, pTable);
		if(list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	/**
	 * 获取凭证文件列表
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getCertificateFileList(Map<String, String> params) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("certificate_id", params.get("certificate_id")));

		String sql = "select * from monitor_data_certificate_file t";
		sql += " where t.certificate_id =@certificate_id";

		return DataUtil.getSelectRows(sql, listParam);
	}

	/**
	 * 报表凭证文件
	 * @param params
	 * @return
	 */
	public int saveCertificateFile(Map<String, String> params) {
		String certificate_id = params.get("certificate_id");
		String fileStr = params.get("files");
		String[] files = fileStr.split(",");

		int row = 0;

		List<GlobalParameter> listParam = new ArrayList<>();

		GlobalParameter p1 = new GlobalParameter("GUID", "");
		GlobalParameter p2 = new GlobalParameter("CERTIFICATE_ID", certificate_id);
		GlobalParameter p3 = new GlobalParameter("FILE_PATH", "");

		listParam.add(p1);
		listParam.add(p2);
		listParam.add(p3);

		try {
			DataUtil.beginTransaction();

			String sql = "delete from monitor_data_certificate_file t where t.certificate_id=@CERTIFICATE_ID";
			row += DataUtil.ExecuteUpdateSql(sql, listParam);

			sql = "insert into monitor_data_certificate_file (GUID,CERTIFICATE_ID,FILE_PATH) values(@GUID,@CERTIFICATE_ID,@FILE_PATH)";
			for (String file : files) {
				p1.setParaValue(UUID.randomUUID().toString());
				p3.setParaValue(file);
				row += DataUtil.ExecuteUpdateSql(sql, listParam);
			}

			DataUtil.commitTransaction();

		} catch (Exception e) {
			logger.error(e.toString());
			DataUtil.rollbackTransaction();
		}

		return row;
	}

	/**
	 * 更新修约数据的凭证
	 * @param params
	 * @return
	 */
	public int updateModifyDataCert(Map<String, String> params) {
		String mn = params.get("mn");// 设备唯一编号
		String data_times = params.get("data_times");
		String certificate_id = params.get("certificate_id");

		if (StringUtil.IsNullOrEmpty(mn) || StringUtil.IsNullOrEmpty(data_times)
				|| StringUtil.IsNullOrEmpty(certificate_id)) {
			return 0;
		}

		String updateSql = "";

		String[] data_time_arr = data_times.split(",");

		String commonUpdate = "update BD_MO_MODIFY_HOUR set certificate_id = '" + certificate_id + "'"
				+ " where mn = '" + mn + "'";
		String timeWhere = "";
		String[] datatimes = null;

		int result = 0;
		try {
			DataUtil.beginTransaction();
			for (String data_time : data_time_arr) {
				datatimes = data_time.split("\\|");
				timeWhere = " and trunc(datatime,'hh24')=trunc(to_date('" + datatimes[0]
						+ "','yyyy-MM-dd HH24:MI:SS'),'hh24')" + " and parmname='" + datatimes[1] + "'";
				updateSql = commonUpdate + timeWhere;
				result += DataUtil.ExecuteUpdateSql(updateSql, null);
			}
			DataUtil.commitTransaction();

		} catch (Exception e) {
			DataUtil.rollbackTransaction();
		}

		return result;
	}
	
	/**
	 * 获取某个站点所有已经修约的数据
	 * 
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getAllModifiedDataList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		String mn = params.get("mn");// 设备唯一编号
		//上传凭证状态
		String modifyDataCertifyStatus = params.get("certificate_id");
		String startDate = params.get("start");
		String endDate = params.get("end");
		String waterType = params.get("WATER_TYPE");
		String tableFlag = params.get("tableFlag");
		String tableName = "";
		if("3".equals(tableFlag)) {
			tableName = "BD_MO_HOUR";
		} else {
			tableName = "BD_MO_DAY";
		}
		String querySql = 
				"select tt.mn,\n" +
						"t4.name,\n" +
						"tt.datatime,\n" + 
						"to_char(tt.modify_date,'yyyy-MM-dd hh24:mi:ss') modify_date,\n" + 
						"tt.modify_certificate_id as guid,\n" + 
						"t2.certificate_name,\n" + 
						"tt.modify_user_id,\n" + 
						"t5.c_userealname modify_user_name\n" + 
						" from "+tableName+" tt\n" + 
						"  left join monitor_data_certificate t2 on tt.modify_certificate_id = t2.guid\n" + 
						"  left join BC_COLLECTION_INFO t3 on tt.mn=t3.device_num\n" + 
						"  left join BC_SITE_INFO t4 on t3.site_id=t4.code\n" + 
						"  left join safe_user t5  on t2.user_id=t5.s_userid\n" + 
						" where 1=1 \n";
		if(mn!=null && !"".equals(mn)) {
			if("1".equals(mn)) {
				querySql +="and tt.mn in ( select t1.device_num \n" + 
						"   from BC_COLLECTION_INFO t1\n" + 
						"   left join BC_SITE_INFO t2 on t1.site_id = t2.code\n" + 
						"  where t2.station_category = '"+waterType+"'\n" + 
						"    and t2.build_status = '0' )";
			} else {
				querySql+= "and tt.MN = '"+mn+"'";
			}
		} else {
			return null;
		}
		if(!startDate.isEmpty()) {
			querySql+= " and tt.modify_date >= to_date('" + startDate + "', 'YYYY-MM-DD HH24:MI:SS') \n";
		}
		if(!endDate.isEmpty()) {
			querySql+= " and tt.modify_date <= to_date('" + endDate + "', 'YYYY-MM-DD HH24:MI:SS') \n";
		}
		querySql+=" and tt.data_from > 1";// 修约数据
		// 修约凭证
		if (!modifyDataCertifyStatus.isEmpty()) {
			if("2".equals(modifyDataCertifyStatus)) {
				querySql += " and tt.MODIFY_CERTIFICATE_ID is not null \n";
			} else if ("3".equals(modifyDataCertifyStatus)) {
				querySql += " and tt.MODIFY_CERTIFICATE_ID is null \n";
			}
		}

		querySql += " group by tt.mn,t4.name,\n" + 
					"tt.datatime,\n" +
					"modify_date,\n" + 
					"tt.modify_certificate_id,\n" + 
					"t2.certificate_name,\n" + 
					"tt.modify_user_id,\n" + 
					"t5.c_userealname\n";

		List<RowBase> result = DataUtil.getPageRow(querySql, null, null, pageInfo);
		return result;
	}
	/**
	 * 根据上传凭证的guid更新数据修约历史表中的凭证ID（包括之前上传凭证和未上传两种情况）
	 * @param certifiedID,新生成的certifiedId
	 * @param GUID,旧的certifiedId
	 * @param MN，站点MN
	 * @param modifyDate,修约时间
	 */
	public int updateModifiedDataCerfifyId (String tableFlag,String certifiedID,String GUID,String MN,String modifyDate) {
		if(StringUtils.isEmpty(MN)||StringUtils.isEmpty(modifyDate)) {
			return 0;
		}
		String tableName="";
		 if(!"".equals(tableFlag) && "3".equals(tableFlag)) {
			 tableName="BD_MO_HOUR";
		 } else if (!"".equals(tableFlag) && "4".equals(tableFlag)){
			 tableName="BD_MO_DAY";
		 }
		String updateSql = "update " +tableName+" t " +" set t.modify_certificate_id = '"+certifiedID+"' \n"+
							" where t.mn= '" +MN+"'"+
							"  and t.modify_date=to_date( '"+modifyDate+"' ,'yyyy-MM-dd hh24:mi:ss')\n";
		if(GUID!=null) {
			updateSql+= "and t.modify_certificate_id ='"+GUID+"'";
		}
		int num = DataUtil.ExecuteUpdateSql(updateSql, null);
		return num;
	}
	/**
	 * 删除凭证文件的时候删除 表中中的modify_certified_id
	 * @param certifiedID
	 * @param MN
	 * @param modifyDate
	 * @return
	 */
	public int updateModifiedCerticateIdToNull (String tableFlag,String certifiedID,String MN,String modifyDate) {
		if(StringUtils.isEmpty(MN)||StringUtils.isEmpty(modifyDate)) {
			return 0;
		}
		String tableName="";
		 if(!"".equals(tableFlag) && "3".equals(tableFlag)) {
			 tableName="BD_MO_HOUR";
		 } else if (!"".equals(tableFlag) && "4".equals(tableFlag)){
			 tableName="BD_MO_DAY";
		 }
		String updateSql = "update " +tableName+" t " +" set t.modify_certificate_id = '' \n"+
							" where t.mn= '" +MN+"'"+
							"  and t.modify_date=to_date( '"+modifyDate+"' ,'yyyy-MM-dd hh24:mi:ss')\n";
		int num = DataUtil.ExecuteUpdateSql(updateSql, null);
		return num;
	}
}
